﻿create database Log4Net
go
use Log4Net
go
CREATE TABLE [dbo].[Log](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NOT NULL,
	[Thread] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Level] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Logger] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Exception] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
create procedure [dbo].[p_Log_DeleteAll] 
as 
delete [Log]
GO
CREATE procedure [dbo].[p_Log_DeleteOne]
(
	@Original_Id int
)
as 
	delete [Log] 
	where @Original_Id = Id
go
create procedure [dbo].[p_Log_SelectOne]
(
	@Id int
) 
as
select top 1
	Id, 
	Date, 
	Thread, 
	[Level], 
	Logger, 
	[Message], 
	Exception 
from 
	[Log] 
where 
	@Id = Id 
go
CREATE procedure [dbo].[p_Log_SelectCount]
(
	@StartId int,
	@EndId int,

	@StartDate datetime,
	@EndDate datetime,

	@ThreadCondition varchar(18),
	@Thread varchar(max),

	@ShowFatal bit,
	@ShowError bit,
	@ShowWarn bit,
	@ShowInfo bit,
	@ShowDebug bit,

	@LoggerCondition varchar(18),
	@Logger varchar(max),

	@MessageCondition varchar(18),
	@Message varchar(max),

	@ExceptionCondition varchar(18),
	@Exception varchar(max),

	@Sort varchar(32),
	@StartRowIndex int,
	@MaximumRows int
)
as
declare @EndRowIndex int
set @EndRowIndex = @StartRowIndex + @MaximumRows
set @StartRowIndex = @StartRowIndex + 1

if @Thread is null set @Thread = ''
if @Message is null set @Message = ''
if @Exception is null set @Exception = ''
if @Logger is null set @Logger = ''

set @Thread = replace(@Thread, '[', '[[]')
set @Thread = replace(@Thread, '%', '[%]')
set @Thread = replace(@Thread, '_', '[_]')

set @Message = replace(@Message, '[', '[[]')
set @Message = replace(@Message, '%', '[%]')
set @Message = replace(@Message, '_', '[_]')

set @Exception = replace(@Exception, '[', '[[]')
set @Exception = replace(@Exception, '%', '[%]')
set @Exception = replace(@Exception, '_', '[_]')

set @Logger = replace(@Logger, '[', '[[]')
set @Logger = replace(@Logger, '%', '[%]')
set @Logger = replace(@Logger, '_', '[_]')

declare @ShowAll bit

set @ShowAll = 0

if @ShowFatal is null set @ShowFatal = 0
if @ShowError is null set @ShowError = 0
if @ShowWarn is null set @ShowWarn = 0
if @ShowInfo is null set @ShowInfo = 0
if @ShowDebug is null set @ShowDebug = 0

if @ShowFatal = 1 and @ShowError = 1 and @ShowWarn = 1 and @ShowInfo = 1 and @ShowDebug = 1
	set @ShowAll = 1
else if @ShowFatal = 0 and @ShowError = 0 and @ShowWarn = 0 and @ShowInfo = 0 and @ShowDebug = 0
	set @ShowAll = 1


	select
		Count(0) [RowCount]
	from 
		[Log]
	where
		(@StartId is null or Id >= @StartId)
		and
		(@EndId is null or Id <= @EndId)
		and
		(@StartDate is null or Date >= @StartDate)
		and
		(@EndDate is null or Date <= @EndDate)
		and
		(
			@Thread = ''
			or (@ThreadCondition = 'Begins with' and Thread like @Thread + '%')
			or (@ThreadCondition = 'Doesn''t begin with' and not Thread like @Thread + '%')
			or (@ThreadCondition = 'Contains' and Thread like '%' + @Thread + '%')
			or (@ThreadCondition = 'Doesn''t contain' and not Thread like '%' + @Thread + '%')
			or (@ThreadCondition = 'Ends with' and Thread like '%' + @Thread)
			or (@ThreadCondition = 'Doesn''t end with' and not Thread like '%' + @Thread)
			or (@ThreadCondition = 'Matches' and Thread = @Thread)
			or (@ThreadCondition = 'Doesn''t match' and not Thread = @Thread)
		)
		and
		(
			(@ShowAll = 1)
			or
			(@ShowFatal = 1 and [Level] = 'FATAL')
			or
			(@ShowError = 1 and [Level] = 'ERROR')
			or
			(@ShowWarn = 1 and [Level] = 'WARN')
			or
			(@ShowInfo = 1 and [Level] = 'INFO')
			or
			(@ShowDebug = 1 and [Level] = 'DEBUG')
		)
		and
		(
			@Logger = ''
			or (@LoggerCondition = 'Begins with' and Logger like @Logger + '%')
			or (@LoggerCondition = 'Doesn''t begin with' and not Logger like @Logger + '%')
			or (@LoggerCondition = 'Contains' and Logger like '%' + @Logger + '%')
			or (@LoggerCondition = 'Doesn''t contain' and not Logger like '%' + @Logger + '%')
			or (@LoggerCondition = 'Ends with' and Logger like '%' + @Logger)
			or (@LoggerCondition = 'Doesn''t end with' and not Logger like '%' + @Logger)
			or (@LoggerCondition = 'Matches' and Logger = @Logger)
			or (@LoggerCondition = 'Doesn''t match' and not Logger = @Logger)
		)
		and
		(
			@Message = ''
			or (@MessageCondition = 'Begins with' and Message like @Message + '%')
			or (@MessageCondition = 'Doesn''t begin with' and not Message like @Message + '%')
			or (@MessageCondition = 'Contains' and Message like '%' + @Message + '%')
			or (@MessageCondition = 'Doesn''t contain' and not Message like '%' + @Message + '%')
			or (@MessageCondition = 'Ends with' and Message like '%' + @Message)
			or (@MessageCondition = 'Doesn''t end with' and not Message like '%' + @Message)
			or (@MessageCondition = 'Matches' and Message = @Message)
			or (@MessageCondition = 'Doesn''t match' and not Message = @Message)
		)
		and
		(
			@Exception = ''
			or (@ExceptionCondition = 'Begins with' and Exception like @Exception + '%')
			or (@ExceptionCondition = 'Doesn''t begin with' and not Exception like @Exception + '%')
			or (@ExceptionCondition = 'Contains' and Exception like '%' + @Exception + '%')
			or (@ExceptionCondition = 'Doesn''t contain' and not Exception like '%' + @Exception + '%')
			or (@ExceptionCondition = 'Ends with' and Exception like '%' + @Exception)
			or (@ExceptionCondition = 'Doesn''t end with' and not Exception like '%' + @Exception)
			or (@ExceptionCondition = 'Matches' and Exception = @Exception)
			or (@ExceptionCondition = 'Doesn''t match' and not Exception = @Exception)
		)
go
CREATE procedure [dbo].[p_Log_SelectPage]
(
	@StartId int,
	@EndId int,

	@StartDate datetime,
	@EndDate datetime,

	@ThreadCondition varchar(18),
	@Thread varchar(max),

	@ShowFatal bit,
	@ShowError bit,
	@ShowWarn bit,
	@ShowInfo bit,
	@ShowDebug bit,

	@LoggerCondition varchar(18),
	@Logger varchar(max),

	@MessageCondition varchar(18),
	@Message varchar(max),

	@ExceptionCondition varchar(18),
	@Exception varchar(max),

	@Sort varchar(32),
	@StartRowIndex int,
	@MaximumRows int
)
as
if @Sort is null or @Sort = '' set @Sort = 'Date DESC'

declare @EndRowIndex int
set @EndRowIndex = @StartRowIndex + @MaximumRows
set @StartRowIndex = @StartRowIndex + 1

if @Thread is null set @Thread = ''
if @Message is null set @Message = ''
if @Exception is null set @Exception = ''
if @Logger is null set @Logger = ''

set @Thread = replace(@Thread, '[', '[[]')
set @Thread = replace(@Thread, '%', '[%]')
set @Thread = replace(@Thread, '_', '[_]')

set @Message = replace(@Message, '[', '[[]')
set @Message = replace(@Message, '%', '[%]')
set @Message = replace(@Message, '_', '[_]')

set @Exception = replace(@Exception, '[', '[[]')
set @Exception = replace(@Exception, '%', '[%]')
set @Exception = replace(@Exception, '_', '[_]')

set @Logger = replace(@Logger, '[', '[[]')
set @Logger = replace(@Logger, '%', '[%]')
set @Logger = replace(@Logger, '_', '[_]')

declare @ShowAll bit

set @ShowAll = 0
if @ShowFatal is null set @ShowFatal = 0
if @ShowError is null set @ShowError = 0
if @ShowWarn is null set @ShowWarn = 0
if @ShowInfo is null set @ShowInfo = 0
if @ShowDebug is null set @ShowDebug = 0

if @ShowFatal = 1 and @ShowError = 1 and @ShowWarn = 1 and @ShowInfo = 1 and @ShowDebug = 1
	set @ShowAll = 1
else if @ShowFatal = 0 and @ShowError = 0 and @ShowWarn = 0 and @ShowInfo = 0 and @ShowDebug = 0
	set @ShowAll = 1

select
	Id,
	Date,
	case
		when len(Thread) > 24 then
			substring(Thread, 0, 21) + '...'
		else Thread
	end Thread,
	[Level],
	case
		when len(Logger) > 24 then
			'...' + substring(Logger, len(Logger) - 20, 21)
		else
			Logger
	End Logger,
	case
		when len([Message]) > 64 then
			substring([Message], 0, 61) + '...'
		else [Message]
	end [Message],
	case
		when len(Exception) > 64 then
			substring(Exception, 0, 61) + '...'
		else Exception
	end Exception
from
(
	select
		ROW_NUMBER() over(
			order by
				case
					when @Sort = 'Id' then Id
				end ASC,
				case
					when @Sort = 'Id DESC' then Id
				end DESC,
				case
					when @Sort = 'Date' then Date
				end ASC,
				case
					when @Sort = 'Date DESC' then Date
				end DESC,
				case
					when @Sort = 'Thread' then Thread
					when @Sort = 'Level' then [Level]
					when @Sort = 'Logger' then Logger
					when @Sort = 'Message' then [Message]
					when @Sort = 'Exception' then Exception
				end ASC,
				case
					when @Sort = 'Thread DESC' then Thread
					when @Sort = 'Level DESC' then [Level]
					when @Sort = 'Logger DESC' then Logger
					when @Sort = 'Message DESC' then [Message]
					when @Sort = 'Exception DESC' then Exception
				end DESC
		) RowNumber,
		Id,
		Date,
		Thread,
		[Level],
		Logger,
		[Message],
		Exception
	from 
		[Log]
	where
		(@StartId is null or Id >= @StartId)
		and
		(@EndId is null or Id <= @EndId)
		and
		(@StartDate is null or Date >= @StartDate)
		and
		(@EndDate is null or Date <= @EndDate)
		and
		(
			@Thread = ''
			or (@ThreadCondition = 'Begins with' and Thread like @Thread + '%')
			or (@ThreadCondition = 'Doesn''t begin with' and not Thread like @Thread + '%')
			or (@ThreadCondition = 'Contains' and Thread like '%' + @Thread + '%')
			or (@ThreadCondition = 'Doesn''t contain' and not Thread like '%' + @Thread + '%')
			or (@ThreadCondition = 'Ends with' and Thread like '%' + @Thread)
			or (@ThreadCondition = 'Doesn''t end with' and not Thread like '%' + @Thread)
			or (@ThreadCondition = 'Matches' and Thread = @Thread)
			or (@ThreadCondition = 'Doesn''t match' and not Thread = @Thread)
		)
		and
		(
			(@ShowAll = 1)
			or
			(@ShowFatal = 1 and [Level] = 'FATAL')
			or
			(@ShowError = 1 and [Level] = 'ERROR')
			or
			(@ShowWarn = 1 and [Level] = 'WARN')
			or
			(@ShowInfo = 1 and [Level] = 'INFO')
			or
			(@ShowDebug = 1 and [Level] = 'DEBUG')
		)
		and
		(
			@Logger = ''
			or (@LoggerCondition = 'Begins with' and Logger like @Logger + '%')
			or (@LoggerCondition = 'Doesn''t begin with' and not Logger like @Logger + '%')
			or (@LoggerCondition = 'Contains' and Logger like '%' + @Logger + '%')
			or (@LoggerCondition = 'Doesn''t contain' and not Logger like '%' + @Logger + '%')
			or (@LoggerCondition = 'Ends with' and Logger like '%' + @Logger)
			or (@LoggerCondition = 'Doesn''t end with' and not Logger like '%' + @Logger)
			or (@LoggerCondition = 'Matches' and Logger = @Logger)
			or (@LoggerCondition = 'Doesn''t match' and not Logger = @Logger)
		)
		and
		(
			@Message = ''
			or (@MessageCondition = 'Begins with' and Message like @Message + '%')
			or (@MessageCondition = 'Doesn''t begin with' and not Message like @Message + '%')
			or (@MessageCondition = 'Contains' and Message like '%' + @Message + '%')
			or (@MessageCondition = 'Doesn''t contain' and not Message like '%' + @Message + '%')
			or (@MessageCondition = 'Ends with' and Message like '%' + @Message)
			or (@MessageCondition = 'Doesn''t end with' and not Message like '%' + @Message)
			or (@MessageCondition = 'Matches' and Message = @Message)
			or (@MessageCondition = 'Doesn''t match' and not Message = @Message)
		)
		and
		(
			@Exception = ''
			or (@ExceptionCondition = 'Begins with' and Exception like @Exception + '%')
			or (@ExceptionCondition = 'Doesn''t begin with' and not Exception like @Exception + '%')
			or (@ExceptionCondition = 'Contains' and Exception like '%' + @Exception + '%')
			or (@ExceptionCondition = 'Doesn''t contain' and not Exception like '%' + @Exception + '%')
			or (@ExceptionCondition = 'Ends with' and Exception like '%' + @Exception)
			or (@ExceptionCondition = 'Doesn''t end with' and not Exception like '%' + @Exception)
			or (@ExceptionCondition = 'Matches' and Exception = @Exception)
			or (@ExceptionCondition = 'Doesn''t match' and not Exception = @Exception)
		)
	) as Page
	where 
		Page.RowNumber between @StartRowIndex and @EndRowIndex